from sqlalchemy import text

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )

from zope.sqlalchemy import ZopeTransactionExtension, mark_changed

from pyramid.security import (
    Allow,
    Authenticated,
    DENY_ALL,
    )


DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()


def execute_query(query, params):
    """Execute query and mark session as changed"""
    DBSession.execute(query, params)
    mark_changed(DBSession())


class RootFactory(object):
    """Defines an ACL for groups/permissions mapping"""
    __acl__ = [ (Allow, Authenticated, 'view'),
                (Allow, 'group:administrators', 'manage'),
                DENY_ALL,
                 ]
    def __init__(self, request):
        pass

def is_member(userid):
    # lire les eleves non GL
    query = """SELECT cd_cli FROM eleves WHERE cd_cli=:userid;"""
    results = DBSession.execute(query, {'userid': userid}).fetchall()
    return results


def get_date_examen(logged_in):
    # lire la date examen de l'eleve
    query = "call spGet_EXAMENS_AFF_PREVU(:logged_in)"
    results = DBSession.execute(query, {'logged_in': logged_in}).first()
    return results


def get_dates_fin_validite(logged_in):
    query = "call spGET_DATES_FIN_VALIDITE(:logged_in)"
    results = DBSession.execute(query, {'logged_in': logged_in}).first()
    return results


def get_eleve_agence_email(logged_in):
    # lire l'adresse email de l'agence de l'eleve
    query = "SELECT a.email FROM eleves e INNER JOIN p_agences a ON e.agence = a.code WHERE cd_cli=:logged_in"
    results = DBSession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_eleve_stage(logged_in):
    # lire les donnees de l'eleve
    query = "call spGet_ELEVES_INFOS(:logged_in)"
    results = DBSession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_eleve_info(userid):
    query = "select nompren, email, permis_demande from eleves where cd_cli=:userid"
    result = DBSession.execute(query, {'userid': userid}).first()
    return {'fullname': result[0],
            'email': result[1],
            'permis': result[2]
            }


def get_eleves_by_code(login):
    # lire l'eleve connecte
    query = """SELECT e.* FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
       WHERE cd_cli=:login AND p.type_examen <> 'GL';"""
    results = DBSession.execute(query, {'login': login}).fetchall()
    return results


def get_eleves_by_last_connection():
    # lire les donnees de l'eleve
    query = """SELECT dern_cnx_le,CD_CLI,NOMPREN,PERMIS_DEMANDE,FORMULE,STATUT,(DEBIT - CREDIT) AS solde,motdepasse 
FROM eleves WHERE dern_cnx_le BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
ORDER BY dern_cnx_le DESC"""
    results = DBSession.execute(query, ).fetchall()
    return results


def get_eleves_by_name(name):
    # lire les donnees de l'eleve
    query = """SELECT e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.statut 
FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
WHERE e.nom like :name AND p.type_examen <> 'GL' ORDER BY e.nompren;"""
    results = DBSession.execute(query, {'name': name + "%"}).fetchall()
    return results


def get_eleve_cpt_extrait(logged_in):
    query = "call spGet_ELEVES_CPT_byEXTRAIT(:logged_in)"
    results = DBSession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_rdv_by_code(logged_in):
    query = "call spGet_ELEVES_RDV(:logged_in)"
    results = DBSession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def update_last_connection(login):
    """Update last connection for login """
    query = "UPDATE eleves SET dern_cnx_le=NOW() WHERE cd_cli=:login;"
    execute_query(query, {'login': login})


def update_details(login, new_values):
    s = ''
    for param in new_values.keys():
        if s:
            s += ",%s=:%s" % (param, param)
        else:
            s = "%s=:%s" % (param, param)
    new_values['login'] = login
    query = u"UPDATE eleves SET %s WHERE CD_CLI=:login" % s
    execute_query(query, new_values)


def update_password(login, password):
    """Update password for member login"""
    query = "UPDATE eleves SET motdepasse=:password WHERE CD_CLI=:login"
    execute_query(query, {'login': login, 'password': password})


